EMT Practice Test

1. Question Content...

---

Question1: Click the Exhibit button.
PRODUCTS
ID NAME QUANTITY
- -- ------------------ ----------------
1 Orange juice 30
2 Milk 25
Given the table shown in the exhibit.
Which values will be returned by query shown below?
SELECT XMLGROUP(
id as "Id",name as "Name",quantity as "Quantity"
OPTION ROW "Product") as Products
FROM products

Question2: Click the Exhibit button.
CREATE TABLE store(sid INTEGER, info XML);
INSERT INTO store VALUES (1,
' <storeinfo sid="1">
< name>ABCDEF</name>
< /storeinfo>');
INSERT INTO store VALUES (2,
' <storeinfo sid="2">
< name>FEDCBA</name>
< /storeinfo>');
Given the statements shown in the exhibit, a user executes the query shown below:
XQUERY for $store in db2-fn:xmlcolumn('STORE.INFO')/storeinfo
let $name := fn:substring($store/name, 1, 3),
$ lcase := fn:lower-case($name)
return $lcase
What is the output?

Question3: Click the Exhibit button.
CREATE TABLE gradereport(sid INTEGER, info XML);
INSERT INTO gradereport VALUES (1,
' <studentinfo sid="1">
< name>John Smith</name>
< honours>No</honours>
< grades>
< course><name>ECE100</name><grade>80</grade></course>
< course><name>CSC100</name><grade>70</grade></course>
< course><name>MAT100</name><grade>60</grade></course>
< /grades>
< phone type="mobile">416-333-8725</phone>
< /studentinfo>');
Given the statements shown in the exhibit, which two queries can be used to return the semester average of the student? (Choose two.)

Question4: A PHP Web application uses the ibm_db2 extension to establish a persistent connection to a remote database.
What must be true of the connections to make full use of the db2_pconnect() API?

Question5: Click the Exhibit button.
CONNECT TO test;
CREATE TABLE parent (empno CHAR(6), firstname VARCAHR(25), lastname VARCHAR ( 25));
CREATE TABLE child (childno CHAR(6), firstname VARCHAR(25), lastname VARCHAR ( 25), empno CHAR(6));
CREATE VIEW employee AS SELECT * FROM parent;
CREATE VIEW employee_children AS SELECT * FROM child;
COMMIT;
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
Which technique can be used to produce a list of all employees and their children, including employees who have no children?

Question6: Which three SQL/XML functions support the default parameter's passing behavior? (Choose three.)

Question7: Which steps are necessary to issue a batch update using a single SQL statement?
(Note: The statement has several sets of input parameter markers and is issued from a database application using the IBM Data Server Driver for JDBC.)

Question8: An application must insert values into a column that has a TIMESTAMP data type.
What is a valid string representation of a TIMESTAMP?

Question9: While developing a Java JDBC application, you use the set of statements shown below:
Connection con;
Statement stmt;
int numUpd;
stmt = con.createStatement();
numUpd = stmt.executeUpdate("UPDATE EMPLOYEE SET PHONENO='4657' WHERE
EMPNO=?");
stmt.close();
con.close();
Assuming a connection has already been established, which statement is true?

Question10: Click the Exhibit button.
< personinfo>
< name>John Wayne</name>
< address type="private">
< city>New York</city>
< country>US</country>
< phone>850-734-6672</phone>
< /address>
< phone type="mobile">796-858-1272 </phone>
< phone type="security">646-252-1053</phone>
< /personinfo>
< personinfo>
< name>Barbara Wayne</name>
< address type="private">
< city>New York</city>
< country>US</country>
< phone>850-734-6672</phone>
< /address>
< phone type="mobile">796-858-1231 </phone>
< phone type="security">646-252-1153</phone>
< /personinfo>
The table PERSON is declared as shown below:
CREATE TABLE person (id BIGINT, info XML)
The documents shown in the exhibit are successfully inserted into the table.
How many phone numbers will be affected by the statement shown below?
UPDATE xmlapp.person
SET info = xmlquery( 'copy $new := $INFO
modify for $j in $new/personinfo//phone return
do replace value of $j with "444-444-4444"
return $new' )
WHERE XMLEXISTS('$INFO/personinfo[name="John Wayne"]')

Question11: An application connecting to a DB2 database using the IBM Data Server Provider for .NET is seeing performance degradation after 100 active users.
Which Visual Basic .NET command string enables the re-use of the database connections?

Question12: A database contains a table and a view declared as shown below:
CREATE TABLE s1.t1 ( c1 INTEGER, c2 CHAR(20) );
CREATE VIEW s1.v1 AS SELECT * FROM s1.t1;
A system administrator successfully executes the following code:
GRANT CONNECT, IMPLICIT_SCHEMA ON DATABASE TO user1;
GRANT INSERT ON TABLE s1.t1 TO user1;
GRANT CONTROL ON s1.v1 TO user1;
Which SQL statement does user USER1 have privileges to execute?

Question13: Function XMLPARSE is used in an INSERT statement.
In which two situations will boundary whitespaces be stripped? (Choose two.)

Question14: Which two techniques guarantee that XML document validation will be performed? (Choose two.)

Question15: A JDBC application uses the "com.ibm.db2.jcc.DB2Driver" driver.
Which two JDBC code snippets illustrate establishing valid connections to a DB2 database named MYDB?
(Choose two.)

Question16: Click the Exhibit button.
CONNECT TO test;
CREATE TABLE tab_a (col1 INT);
CREATE TABLE tab_b (col1 INT);
INSERT INTO tab_a VALUES (1), (2), (3), (4);
INSERT INTO tab_b VALUES (1), (1), (2), (2);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
If the statement shown below is executed:
UPDATE tab_a SET col1 = 10 WHERE col1 IN (SELECT * FROM tab_b);
How many rows in table TAB_A will be modified?

Question17: User USER1 successfully creates a table USER1.TABLE1 and successfully creates a package USER1.PKG1 for an application containing the SQL shown below:
EXEC SQL UPDATE user1.table1 SET col1 = :hvar1 WHERE col1 IS NULL
Assuming user USER2 can connect to the database, but has no other relevant authorities, which command must user USER1 issue to permit user USER2 to run the application?

Question18: Which two SQL statements demonstrate the correct usage of a parameter marker? (Choose two.)

Question19: The scope of a cursor is limited to ____.

Question20: While developing a CLI application, you use the code shown below:
SQLCHAR *stmt = (SQLCHAR *)"DELETE FROM org WHERE deptnumb = ? ";
cliRC = SQLSetStmtAttr(hstmt, SQL_ATTR_DEFERRED_PREPARE,
SQL_DEFERRED_PREPARE_ON )
cliRC = SQLPrepare(hstmt, stmt, SQL_NTS);
Now, the ORG table does not exist in the database.
What will be the value of "cliRC" after executing the SQLPrepare command?

Question21: Which three data types are supported as output of the XMLSERIALIZE function? (Choose three.)

Question22: In a query, which clause can reference a CLOB data type?

Question23: Which statement is true of an application that only uses static SQL?

Question24: Which two commands can be used to make use of the latest statistics? (Choose two.)

Question25: Click the Exhibit button.
CREATE TABLE gradereport(sid INTEGER, info XML);
INSERT INTO gradereport VALUES (1,
' <studentinfo sid="1">
< name>John Smith</name>
< honours>No</honours>
< grades>
< course><name>ECE100</name><grade>80</grade></course>
< course><name>CSC100</name><grade>70</grade></course>
< course><name>MAT100</name><grade>75</grade></course>
< /grades>
< phone type="mobile">416-333-8725</phone>
< /studentinfo>');
Given the statements shown in the exhibit, a user wishes to obtain the following result:
< student><name>John Smith</name><phone>416-333-8725</phone></student>
Which query can the user execute to achieve this?

Question26: Table T1 and view V1 were created by executing the statements shown below:
CREATE TABLE t1 (c1 INT CHECK (c1 < 275),
c2 CHAR(3));
CREATE VIEW v1 AS SELECT c1, c2 FROM t1 WHERE c1 > 250
WITH LOCAL CHECK OPTION;
Which statement will execute successfully?

Question27: The table PERSON is declared as shown below:
CREATE TABLE xmltest (id BIGINT, info XML)
What is the column type for the result of the following statement?
SELECT t.* FROM
xmltest,
XMLTABLE (
' $INFO/question'
) AS t

Question28: Given the two tables shown below:
COUNTRIES
----------
COUNTRY CONTINENT_ID
Greece 1
Germany 1
Canada 2
CONTINENTS
-----------
ID CONTINENT
1 Europe
2 North America
3 Asia
A user wants the following result:
COUNTRY CONTINENT
Greece Europe
Which query should the user submit to achieve the result?

Question29: Click the Exhibit button.
CONNECT TO test;
CREATE TABLE tab_a (col1 INT);
CREATE TABLE tab_b (col1 INT);
INSERT INTO tab_a VALUES (1), (2), (3), (4);
INSERT INTO tab_b VALUES (1), (2), (2), (3);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit executed successfully.
If the statement shown below is executed:
DELETE FROM tab_a WHERE col1 IN (SELECT DISTINCT * FROM tab_b);
How many rows will be removed from table TAB_A?

Question30: The statement below was used to create a cursor named CSR1:
DECLARE csr1 CURSOR FOR SELECT * FROM employee FOR UPDATE OF job;
When the cursor is opened, the result set contains six rows. After fetching all of the rows in the result set associated with cursor CSR1, application TEST1 needs to fetch the third row of the result set again.
Which steps will allow application TEST1 to fetch the third row again?

Question31: Click the Exhibit button.
The database shown below exists on a remote server:
Database name: MYDB
Host IP: 12.34.56.78
DB2 Port: 56789
User: remote
password: password
This database has been cataloged on the local server with an alias of MYREMDB.
You are developing a CLI/ODBC application that must establish a connection to the remote database shown in the exhibit.
What are two ways by which you can connect to the database? (Choose two.)

Question32: Which combination of statements can be used to delete qualifying rows from a table named EMP?

Question33: Which type of join condition specifies that the result table will contain a row for each row from the table to the left, concatenated with each row from the table to the right?

Question34: What is the final result of executing the statements shown below?
CREATE SEQUENCE order_seq
START WITH 100
INCREMENT BY 25
MAXVALUE 150
CYCLE;
VALUES NEXT VALUE FOR order_seq;
VALUES NEXT VALUE FOR order_seq;
VALUES NEXT VALUE FOR order_seq;
VALUES NEXT VALUE FOR order_seq;

Question35: Which statement(s) will create and bind the package for the program myprogram.sqc?

Question36: Which set of SQL statements must be run prior to the EXECUTE statement shown below so that the INSERT statement can be executed successfully?
EXECUTE s1 USING :v1;

Question37: Table T1 and views V1 and V2 were created by executing the statements shown below:
CREATE TABLE t1 (c1 INT, c2 CHAR(3));
CREATE VIEW v1 AS SELECT c1, c2 FROM t1 WHERE c1 > 100;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE c2 IS NULL WITH
CASCADED CHECK OPTION;
Which statement will execute successfully?

Question38: While developing a Java JDBC application, you use the set of statements shown below:
Connection con;
PreparedStatement pstmt;
int numUpd;
pstmt = con.prepareStatement("UPDATE EMPLOYEE SET PHONENO='4657' WHERE
EMPNO=?");
pstmt.setString(0, "000010");
numUpd = pstmt.executeUpdate();
pstmt.close();
Assuming a connection has already been established, which statement is true?

Question39: Given the two tables shown below:
TAB1
LETTER GRADE
A 80
B 70
C 60
TAB2
LETTER GPA
A 4
C 2
D 1
and the successful execution of the following query:
SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON tab1.letter = tab2.letter;
How many rows will be returned?

Question40: The table shown below exists in the database:
CREATE TABLE team.workitems ( priority INTEGER, last_update TIMESTAMP, details XML )
User USER1 has CREATEIN authority on the TEAM schema and can SELECT from the TEAM.WORKITEMS table. An SQL routine is created that performs a positioned update using a cursor declared as shown below:
DECLARE c_workitem CURSOR FOR SELECT priority, last_update FROM team.workitems FOR UPDATE;
Which additional privilege is required?

Question41: Click the Exhibit button.
VEMPLOYEE
- ------------------
EMPNO CHAR(6)
LASTNAME VARCHAR(30)
SALARY DEC(9,2)
DEPTNO CHAR(3)
VDEPARTMENT
----------------------
DEPTNO CHAR(3)
DEPTNAME VARCHAR(20)
MGRNO CHAR(6)
VPROJECT
- ---------------
PROJNO CHAR(6)
PROJNAME VARCHAR(20)
DEPTNO CHAR(6)
RESPEMP CHAR(6)
A JOIN operation references three views in the exhibit; the SELECT statement used does not contain a WHERE clause.
When the SELECT statement is executed, what will the result set contain?

Question42: Click the Exhibit button.
< personinfo>
< name>John Smith</name>
< address type="private">
< city>New York</city>
< country>US</country>
< phone>850-734-6672</phone>
< /address>
< phone type="mobile">796-858-1272 </phone>
< phone type="security">646-252-1053</phone>
< /personinfo>
The table PERSON is declared as shown below:
CREATE TABLE person (id BIGINT, info XML)
The document shown in the exhibit is successfully inserted into the table.
How many rows will be returned for the following statement?
SELECT t.* FROM
xmlapp.PERSON,
XMLTABLE (
' $INFO/personinfo'
COLUMNS
name VARCHAR(30) PATH './name',
phone VARCHAR(30) PATH './phone'
) AS t

Question43: A CLI/ODBC application contains the lines of code shown below:
SQLHANDLE hstmt; /* statement handle */
SQLCHAR *stmt = (SQLCHAR *)"DELETE FROM org WHERE deptnumb = ? ";
SQLSMALLINT parameter1=5;
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
SQLSetStmtAttr(hstmt, SQL_ATTR_DEFERRED_PREPARE, SQL_DEFERRED_PREPARE_OFF ); If table ORG does not exist, which two CLI/ODBC APIs will return SQL_ERROR in the variable RC?
(Choose two.)

Question44: Which statement should be used to obtain the result set shown below?
< Root>
< First>Hello</First>
< Second>World !</Second>
< ?Test Pass test?>
< /Root>

Question45: A table named INVENTORY was created by executing the SQL statement shown below:
CREATE TABLE inventory (part_no INTEGER,
quantity INTEGER,
price DECIMAL(7,2),
status CHAR(2));
If items are indicated to be out of stock by setting STATUS to NULL and QUANTITY to zero, which SQL statement updates the INVENTORY table to indicate that all items with part numbers below 150 are out of stock?

Question46: What are two valid connection options that you can use while creating a connection in PHP using the IBM_DB2 extension? (Choose two.)

Question47: Given the two tables shown below:
TAB1
LETTER GRADE
A 80
B 70
C 60
TAB2
LETTER GPA
A 4
C 2
D 1
and the successful execution of the following query:
SELECT * FROM tab1 FULL OUTER JOIN tab2 ON tab1.letter = tab2.letter;
How many rows will be returned?

Question48: Given the SQL statement shown below:
UPDATE employee SET lastname = CAST(? AS VARCHAR(12))
WHERE empno = ?00050??
What does CAST(? AS VARCHAR(12)) represent?

Question49: You have set your data types as CHAR(10), VARCHAR(20). Given the operation shown below:
CHAR(10) UNION VARCHAR(20)
What will be the final data type?

Question50: Given the SQL statement shown below:
UPDATE address SET number_street
( SELECT address FROM employee
WHERE address.empid = employEE .empid)
WHERE number_street IS NULL
Which comment is true?

Question51: Click the Exhibit button.
CONNECT TO test;
CREATE TABLE project (projno CHAR(6), deptno CHAR(3), projname VARCHAR(20)); CREATE TABLE employee (empno CHAR(6), lastname VARCHAR(25), deptno CHAR(3)); COMMIT;
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
Which query will produce a list of all projects and all employees assigned to projects and also include employees that have not been assigned a project?

Question52: Which two C# .NET code snippets illustrate the valid execution of a SQL statement through the IBM Data Server Provider for .NET? (Choose two.)

Question53: Click the Exhibit button.
CREATE TABLE prod_type (prod_type VARCHAR(10), description VARCHAR (30)); CREATE TABLE prod_descr(info XML);
INSERT INTO prod_descr VALUES
( '<product id="1">
< name>Orange juice</name>
< company>Orange Garden</company>
< type>Drinks</type>
< /product>'),
( '<product id="2">
< name>Coke</name>
< company>Best drinks</company>
< type>Drinks</type>
< type>Premium</type>
< /product>');
INSERT INTO prod_type VALUES ('Drinks','Drinks');
You execute the SQL statement shown in the exhibit.
Which two statements will return rows? (Choose two.)